﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spBkp_Full]
#-- Purpose:		Performs a full backup of a database.  Chooses between
#--					LiteSpeed if available, otherwise standard Backup
#--	Last Update:	07/14/2016
#--					For a complete history - please review comments in Version
#--					Control.
#-- Optional XPs:	[master].[dbo].[xp_backup_database]
#-- Called By:		[dbo].[spBkp_FullAll]
#--					[dbo].[spBkp_Differential]
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spBkp_Full]
(
	@database_name	sysname,
	@verify			bit					= 1,
	@label			nvarchar(128)		= NULL,
	@ignore_skip	bit					= 0
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE		@dir varchar(2000), @sCommand nvarchar(2000), @file_name varchar(2000),
			@offline bit, @is_standby bit, @current_date datetime, @job_name_filter varchar(125),
			@hadr_enabled bit, @preferred_replica bit, @sCommandWith nvarchar(2000),
			@sSQL varchar(MAX), @major int
DECLARE		@progname sysname
DECLARE @tAOAGStat TABLE (
	[is_primary] bit,
	[database_id] int,
	[database_name] nvarchar(256),
	[automated_backup_preference] int,
	[automated_backup_preference_desc] nvarchar(256)
)
DECLARE @tPreferredReplica TABLE ([yesno] bit)

--- Define the name of this program (for logs)
SELECT		@progname = N'spBkp_Full'

--- Ensure the database skips backup if configured not to backup
IF ISNULL(@ignore_skip, 0) = 0 
	AND EXISTS (SELECT TOP 1 database_name FROM [dbo].[vwMaint_DoNotBackup] WHERE database_name = @database_name)
  BEGIN
	RETURN
  END

--- @database_name cannot be tempdb
SELECT	@database_name = LTRIM(RTRIM(ISNULL(@database_name, '')))
IF @database_name = N'tempdb'
  BEGIN
	PRINT 'tempdb database cannot be backed up (' + @database_name + ')'
	RETURN
  END

--- @database_name is a mandatory fields
IF @database_name = N''
  BEGIN
	RAISERROR ('The name of the database to backup is required (@database_name)', 16, 1)
	RETURN
  END

--- @database_name must be an existing database
IF NOT EXISTS (SELECT TOP 1 name FROM [master].[sys].[databases] WHERE name = @database_name)
  BEGIN
	RAISERROR ('The requested database does not exist', 16, 1)
	RETURN
  END

--- Snapshot databases cannot be backed up
IF EXISTS (SELECT TOP 1 name FROM [master].[sys].[databases] WHERE name = @database_name AND source_database_id IS NOT NULL)
  BEGIN
	PRINT 'Snapshot databases cannot be backed up (' + @database_name + ')'
	RETURN
  END

BEGIN TRY
	--- Get Current date/time stamp and set base job name
	SELECT	@current_date = GETDATE(),
			@job_name_filter = 'DBA: Backup Full',
			@major = CAST(PARSENAME(CONVERT(varchar,SERVERPROPERTY('ProductVersion')),4) AS int)

	--- Update the readonly flag if this is a HADR database (SQL 2012+)
	IF @major > 10
	  BEGIN
		IF OBJECT_ID('[sys].[dm_hadr_availability_group_states]') IS NOT NULL
			SELECT @sSQL = 'SELECT		(CASE WHEN t1.[replica_server_name] = t4.[primary_replica] THEN 1 ELSE 0 END) [is_primary],
			t2.[database_id],
			t2.[name] [database_name],
			t3.[automated_backup_preference],
			t3.[automated_backup_preference_desc]
FROM		[sys].[availability_replicas] t1
JOIN		[sys].[databases] t2 ON t1.[replica_id] = t2.[replica_id]
JOIN		[sys].[availability_groups] t3 ON t1.[group_id] = t3.[group_id]
LEFT JOIN	[sys].[dm_hadr_availability_group_states] t4 ON t1.[group_id] = t4.[group_id]'
		ELSE
			SELECT	@sSQL = 'SELECT 0, 0, SPACE(0), 0, SPACE(0) WHERE 1 = 0'

		INSERT INTO @tAOAGStat EXEC (@sSQL)
	  END
	
	--- Ensure the directory exists  (xp)
	EXEC	[dbo].[spBkp_GetDirectory] @database_name = @database_name, @backup_directory_name = @dir OUTPUT 
	EXEC	[dbo].[spIO_CreateDirectory] @dir

	--- Get File Name
	EXEC	[dbo].[spBkp_GetFullBackupName] @database_name = @database_name, @backup_directory_name = @file_name OUTPUT

	--- Determine database status
	SELECT	@offline = ISNULL( [dbo].[fnConfig_IsDatabaseOffline] ( @database_name ), 0 ),
			@is_standby = ISNULL( [dbo].[fnConfig_IsDatabaseStandby] ( @database_name ), 0 ),
			@hadr_enabled = ISNULL(CAST(SERVERPROPERTY('IsHadrEnabled') as bit), 0),
			@preferred_replica = 1
	
	--- Is this the preferred backup location
	IF OBJECT_ID('[sys].[dm_hadr_availability_group_states]') IS NOT NULL
		BEGIN
		SELECT	@sSQL = REPLACE('SELECT preferred_replica = [sys].[fn_hadr_backup_is_preferred_replica](''[VAR_DBNAME]'')', '[VAR_DBNAME]', @database_name)
		INSERT INTO @tPreferredReplica EXEC(@sSQL)
		SELECT @preferred_replica = [yesno] FROM @tPreferredReplica
		END

	IF @offline = 0 AND @is_standby = 0 AND @database_name <> N'tempdb' AND (
			@hadr_enabled = 0
			OR (@hadr_enabled = 1 AND @preferred_replica = 1)
		)
	  BEGIN
		--- Build the command to backup the database
		IF EXISTS(select * from [master].[dbo].[sysobjects] where id = object_id(N'[master].[dbo].[xp_backup_database]'))
		  BEGIN
			SELECT	@sCommand = 'EXEC [master].[dbo].[xp_backup_database] @database = ''' + @database_name + ''', @filename = ''' + @file_name + ''''

			IF @label IS NOT NULL
				SELECT	@sCommand = @sCommand + ', @desc=''' + @label + ''''
		  END
		ELSE
		  BEGIN
			SELECT	@sCommand = 'BACKUP DATABASE [' + @database_name + '] TO DISK = ''' + @file_name + ''''

			--- Set the backup label
			IF @label IS NOT NULL
				SELECT	@sCommandWith = 'NAME = ''' + @label + ''''
			
			--- Verify the validity of the database data blocks
			IF @verify = 1
				IF @sCommandWith IS NULL
					SELECT @sCommandWith = 'CHECKSUM'
				ELSE
					SELECT @sCommandWith = @sCommandWith + ', CHECKSUM'

			--- Copy_Only on Secondary AlwaysOn Availability Groups
			IF EXISTS (SELECT TOP 1 [is_primary] FROM @tAOAGStat WHERE [database_name] = @database_name AND [is_primary] = 0)
				IF @sCommandWith IS NULL
					SELECT @sCommandWith = 'COPY_ONLY'
				ELSE
					SELECT @sCommandWith = @sCommandWith + ', COPY_ONLY'
				
			--- Add the "WITH" statement to the restore command
			IF @sCommandWith IS NOT NULL
				SELECT	@sCommand = @sCommand + ' WITH ' + @sCommandWith

			--- Validity the backup
			SELECT @sCommand = @sCommand + '; RESTORE VERIFYONLY FROM DISK = ''' + @file_name + ''''
		  END

		--- Execute the command (backup the database)
		IF LEN(@sCommand) > 0
		  BEGIN
			EXEC	(@sCommand)
			EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sCommand
		  END
	  END
END TRY
BEGIN CATCH
	--- Log the error - if possible
	DECLARE @sResults varchar(MAX)
	SELECT @sResults = '[' + @progname + '] The database [' + @database_name + '] failed to backup (' + ISNULL(@sCommand, 'Unknown') + ').' 
	IF ERROR_NUMBER() IS NOT NULL
		SELECT @sResults = @sResults + CHAR(13)
						+ 'ErrNo: ' + ISNULL(CAST(ERROR_NUMBER() as varchar(50)), '') + CHAR(13)
						+ 'ErrMsg: ' + ISNULL(ERROR_MESSAGE(), '') + CHAR(13)
						+ 'Proc: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(13)
						+ 'LineNo: ' + ISNULL(CAST(ERROR_LINE() as varchar(10)), '')

	--- Print the error message to sysout
	PRINT @sResults

	--- Insert the error in the database
	IF @sResults IS NOT NULL
		BEGIN
		INSERT INTO [dbo].[tblMaint_Log] (mnt_table_catalog, mnt_action_date, mnt_action_type, mnt_success, mnt_results)
			VALUES (@database_name, GETDATE(), N'backup_full', 0, @sResults)

		--- Must raise an error so sqlcmd catches the severity level
		RAISERROR (@sResults, 16, 1)
		RETURN
		END
	ELSE
		BEGIN
		--- Must raise an error so sqlcmd catches the severity level
		SELECT @sResults = '[' + @progname + '] The database [' + ISNULL(@database_name, '') + '] failed to backup (' + ISNULL(@sCommand, '') + ').'
		RAISERROR (@sResults, 16, 1) WITH LOG
		RETURN
		END
END CATCH

SET NOCOUNT OFF
